﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using CodeSample.Model;

namespace CodeSample.Database
{
    class DBAdapterMan
    {
        private OleDbConnection con;

        public DBAdapterMan(string DBName, string password)
        {
            con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; data source=" + DBName + @";Jet OLEDB:Database Password=" + password);
            con.Open();
        }

        public OleDbDataReader GetTable(string tableName)
        {
            OleDbDataReader reader = null;

            OleDbCommand cmd = new OleDbCommand("SELECT * FROM " + tableName, con);
            reader = cmd.ExecuteReader();

            return reader;
        }

        //public OleDbDataReader GetLanguage()
        //{
        //    OleDbDataReader reader = null;

        //    //OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT Language FROM Sample GROUP BY Language", con);
        //    //DataSet dataSet = new DataSet();
        //    //dataAdapter.Fill(dataSet);

        //    OleDbCommand cmd = new OleDbCommand("SELECT Language FROM Sample GROUP BY Language", con);
        //    reader = cmd.ExecuteReader();

        //    return reader;
        //}

        public void InsertRecord(string tableName, params string[] values)
        {
            string sqlCommand = string.Format("INSERT INTO {0} VALUES(", tableName);

            for (int i = 0; i < values.Length - 1; i++)
            {
                sqlCommand += string.Format("'{0}', ", values[i]);
            }

            sqlCommand += string.Format("'{0}')", values[values.Length - 1]);

            OleDbCommand cmd = new OleDbCommand(sqlCommand, con);
            cmd.ExecuteNonQuery();
        }

        public void InsertSampleRecord(SampleMan aSample)
        {
            OleDbCommand cmd = new OleDbCommand("INSERT INTO Sample VALUES (?, ?, ?, ?, ?, ?, ?)", con);
            cmd.Parameters.AddWithValue("@ID", aSample.Id);
            cmd.Parameters.AddWithValue("@Language", aSample.Language);
            cmd.Parameters.AddWithValue("@Name", aSample.Name);
            cmd.Parameters.AddWithValue("@Code", aSample.Code);
            cmd.Parameters.AddWithValue("@Author", aSample.Description.Author);
            cmd.Parameters.AddWithValue("@Purpose", aSample.Description.Purpose);
            cmd.Parameters.AddWithValue("@Details", aSample.Description.Details);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }

        public void UpdateSampleRecord(SampleMan aSample)
        {
            OleDbCommand cmd = new OleDbCommand("UPDATE Sample SET [Language] = ?, [Name] = ?, [Code] = ?, [Purpose] = ?, [Details] = ?, [Author] = ? WHERE [ID] = ?", con);
            cmd.Parameters.AddWithValue("@Language", aSample.Language);
            cmd.Parameters.AddWithValue("@Name", aSample.Name);
            cmd.Parameters.AddWithValue("@Code", aSample.Code);
            cmd.Parameters.AddWithValue("@Purpose", aSample.Description.Purpose);
            cmd.Parameters.AddWithValue("@Details", aSample.Description.Details);
            cmd.Parameters.AddWithValue("@Author", aSample.Description.Author);
            cmd.Parameters.AddWithValue("@ID", aSample.Id);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }

        public void DeleteSampleRecord(SampleMan aSample)
        {
            OleDbCommand cmd = new OleDbCommand("DELETE FROM Sample WHERE [ID] = ?", con);
            cmd.Parameters.AddWithValue("@ID", aSample.Id);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }

        public void CloseConnection()
        {
            if (con.State != ConnectionState.Closed)
                con.Close();
        }
    }
}
